﻿if exists (select * from dbo.sysobjects where id = object_id(N'dbo.SelectChangeHistory') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.SelectChangeHistory

go

CREATE PROCEDURE dbo.SelectChangeHistory
	@SourceTableId INT,
	@SourceRowID BIGINT
AS

DECLARE @SourceTableName VARCHAR(128)
SET @SourceTableName = NULL

SELECT @SourceTableName = NAME FROM dbo.SourceTable WHERE ID = @SourceTableId

IF @SourceTableName is NULL
BEGIN
	RAISERROR('Invalid source table ID passed in @SourceTableId argument.',16 ,1)
	RETURN
END

EXEC('
SELECT
	A.ID ChangeTransactionId,
	A.TrackingMask,
	A.UserName,
	A.HostName,
	A.AppName,
	A.ChangeDate,
	A.Spid,
	A.CmdType,
	B.SourceColumnId,
	B.SourceFieldValue
FROM
	dbo.ChangeTransaction A INNER JOIN dbo.[ChangedData__' + @SourceTableName + '] B ON A.ID=B.ChangeTransactionId
WHERE
	A.SourceTableId = ' + @SourceTableId + ' AND
	B.SourceRowId = ' + @SourceRowID + '
ORDER BY
	dbo.GetCmdTypeOrder(A.CmdType), A.ID
')